CREATE DATABASE Horizon
go

use Horizon
go

CREATE TABLE  Sector
 (
	Sector              char         (5)  NOT NULL,
	Description         varchar     (50)  NOT NULL 
) 
go

CREATE TABLE Flight
(
	FlightNo            char (6) NOT NULL,
	Sector              char (5) NOT NULL,
	Date				datetime		 ,
	AircraftTypeID      char (4) NOT NULL,
	LocalDepartureTime  char (5) NOT NULL, 
	LocalArrivalTime    char (5) NOT NULL,
	FareID				  int    NOT NULL,
	FirstSeats            int    NOT NULL,
	BusinessSeats         int    NOT NULL,
	EconomySeats          int    NOT NULL  
)
GO

CREATE TABLE Aircraft 
(
	 AircraftTypeID        char     (4) NOT NULL,
	 Description           varchar (30) NOT NULL,
	 Amount                varchar  (3) NOT NULL,
	 FirstSeats            int          NOT NULL,
	 BusinessSeats         int          NOT NULL,
	 EconomySeats          int          NOT NULL
)
GO

CREATE TABLE Fare
(
	FareID              int identity(1,1) NOT NULL,
	FirstClassFare      money    NOT NULL,
	BusinessClassFare   money    NOT NULL,
	EconomyClassFare    money    NOT NULL 
)
GO


CREATE TABLE Passenger 
(
	 PNRNo     int NOT NULL,
	 FlightNo  char (6) NOT NULL,
	 FName     varchar (15) NOT NULL,
	 LName     varchar (15) NOT NULL,
	 Age       int          NOT NULL,
	 Gender    char     (1) NOT NULL,
	 Class     char     (1) NOT NULL,
	 SeatPref  char     (1)     NULL,
	 MealPref  varchar (20)     NULL,
	 SSR       varchar(100) NOT NULL,
	 Status    char     (1) NOT NULL  
)
GO

CREATE TABLE  DailyCollection
 (
	TranID	  int  identity(1,1),
	PNRNo     int   NOT NULL,
	TranDate  datetime          NOT NULL ,
	TranType  char         (1) NOT NULL ,
	Amount     money            NOT NULL 
)
go


ALTER TABLE  Sector
ADD CONSTRAINT  pkSector PRIMARY KEY(Sector)
go

ALTER TABLE Flight
ADD CONSTRAINT pkFlight PRIMARY KEY(FlightNo)
go

ALTER TABLE Flight
ADD CONSTRAINT fkFlightSector FOREIGN KEY (Sector)
REFERENCES Sector(Sector)
go 

ALTER TABLE Aircraft
ADD CONSTRAINT pkAircraftTypeID PRIMARY KEY (AircraftTypeID)
go

ALTER TABLE Flight
ADD CONSTRAINT fkFlightAircraft FOREIGN KEY (AircraftTypeID)
REFERENCES Aircraft(AircraftTypeID)
go

ALTER TABLE Fare
ADD CONSTRAINT pkFare PRIMARY KEY (FareID)
go

ALTER TABLE Flight
ADD CONSTRAINT fkFlightFare FOREIGN KEY (FareID)
REFERENCES Fare(FareID)
go

ALTER TABLE Passenger
ADD CONSTRAINT pkPNRNo PRIMARY KEY (PNRNo)
go

ALTER TABLE Passenger
ADD CONSTRAINT fkPassengersFlight FOREIGN KEY (FlightNo)
REFERENCES Flight(FlightNo)
go

ALTER TABLE DailyCollection
ADD CONSTRAINT pkDailyCollection PRIMARY KEY (TranID)
go

ALTER TABLE DailyCollection
ADD CONSTRAINT fkDailyCollectionsPassenger FOREIGN KEY (PNRNo)
REFERENCES Passenger(PNRNo)
go
